package com.rdc.example.dao.impl;

import com.rdc.example.dao.CommentDao;
import com.rdc.example.entity.Comment;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static com.rdc.example.util.DBUtil.closeResources;
import static com.rdc.example.util.DBUtil.getConnection;

/**
 * @author duck
 */
public class CommentDaoImpl implements CommentDao {

    public static void main(String[] args) throws Exception {
        CommentDaoImpl commentDao = new CommentDaoImpl();
        Comment comment = new Comment();
        List<Comment> commentList = new ArrayList<>();

        List<Comment> list= commentDao.listCommentByArticleId(2);
        System.out.println(list);
    }

    /**
     *
     * @param comment
     * @return
     * @throws Exception
     */
    @Override
    public boolean insertComment(Comment comment) throws Exception {
        // 进行数据库插入操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        try {
            conn = getConnection();
            String sql ="INSERT INTO comment (article_id ,user_id,content) VALUES (?,?,?)";
            pstmt = conn.prepareStatement(sql);
            insert(pstmt, comment);
            int row = pstmt.executeUpdate();
            return row > 0;
        }catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            closeResources(null, pstmt, conn);
        }
    }

    /**
     *
     * @param id
     * @return 结果
     * @throws Exception
     */
    @Override
    public boolean deleteById(Integer id) throws Exception {
        // 进行数据库删除操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        //ResultSet rs=null;
        try {
            conn = getConnection();
            String sql = "delete from comment where id=?" ;
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1, id);
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (SQLIntegrityConstraintViolationException e) {
            e.printStackTrace();
        }finally {
            closeResources(null,pstmt,conn);
        }
        return false;
    }

    public boolean deleteByArticleId(Integer articleId) throws Exception {
        // 进行数据库删除操作
        Connection conn =null;
        PreparedStatement pstmt=null;
        //ResultSet rs=null;
        try {
            conn = getConnection();
            String sql = "delete from comment where article_id=?" ;
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1, articleId);
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (SQLIntegrityConstraintViolationException e) {
            e.printStackTrace();
        }finally {
            closeResources(null,pstmt,conn);
        }
        return false;
    }

    @Override
    public boolean addLike(Integer id) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            String sql = "update comment set likes = likes + 1 where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean reduceLike(Integer id) throws Exception {
          Connection conn = null;
          PreparedStatement pstmt = null;
          try {
              conn = getConnection();
              //保证不会减少到负数
              String sql = "update comment set likes = greatest(likes - 1, 0) where id = ?";
              pstmt = conn.prepareStatement(sql);
              pstmt.setInt(1, id);
              int row = pstmt.executeUpdate();
              return row > 0;
          } catch (SQLException e) {
              throw new RuntimeException(e);
          } finally {
              closeResources(null, pstmt, conn);
          }
      }


    /**
     *
     * @param articleId
     * @return
     * @throws Exception
     */
    @Override
    public List<Comment> listCommentByArticleId(Integer articleId) throws Exception {
        // 进行数据库查询操作
        Connection conn = getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Comment> commentList = new ArrayList<>();
        Comment comment = new Comment();
        try {
            String sql = "SELECT * from comment where article_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, articleId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                comment=get(rs);
                commentList.add(comment);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(rs, pstmt, conn);
        }
        return commentList;
    }

    /**
     *
     * @param articleId
     * @return
     * @throws Exception
     */
    @Override
    public List<Comment> listCommentByArticleIdSortByCreatedAtDesc(Integer articleId) throws Exception {
        // 进行数据库查询操作
        Connection conn = getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Comment> commentList = new ArrayList<>();
        Comment comment = new Comment();
        try {
            String sql = "SELECT * from comment where article_id=? order by created_at desc";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, articleId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                comment=get(rs);
                commentList.add(comment);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(rs, pstmt, conn);
        }
        return commentList;
    }

    /**
     *
     * @param articleId
     * @return
     * @throws Exception
     */
    @Override
    public List<Comment> listCommentByArticleIdSortByCreatedAtAsc(Integer articleId) throws Exception {
        // 进行数据库查询操作
        Connection conn = getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        List<Comment> commentList = new ArrayList<>();
        Comment comment = new Comment();
        try {
            String sql = "SELECT * from comment where article_id=? order by created_at asc";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, articleId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                comment=get(rs);
                commentList.add(comment);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(rs, pstmt, conn);
        }
        return commentList;
    }




    private void insert(PreparedStatement pstmt, Comment comment) throws SQLException {
        pstmt.setInt(1, comment.getArticleId());
        pstmt.setInt(2, comment.getUserId());
        pstmt.setString(3, comment.getContent());
    }

    private Comment get(ResultSet rs) throws SQLException {
        Comment comment = new Comment();
        comment.setId(rs.getInt("id"));
        comment.setArticleId(rs.getInt("article_id"));
        comment.setUserId(rs.getInt("user_id"));
        comment.setContent(rs.getString("content"));
        comment.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        comment.setLikes(rs.getInt("likes"));
        return comment;
    }

}
